////////////////////////////////////////////////////////////////////////////////
// Copyright 2006 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   ITEM_LINE
// AUTHOR           :   Alex Cheung
// DATE CREATE      :   2006/01/19
// DESCRIPTION      :   a DAO file for the function, ITEM_LINE
//                  :
//                  :
//                  :
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2006/01/19   Alex Cheung     1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.catalog.itemLine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.common.ProjectConstants;
import com.erp.app.catalog.itemLine.criteria.ItemLineSrchCriteria;
import com.erp.app.catalog.itemLine.pk.ItemLinePk;
import com.erp.app.catalog.itemLine.vo.ItemLineVo;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.util.CommonUtil;

/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Alex Cheung
* @version 1.0     2006/01/19
* @since           FrameWork 1.0
*
*/
public class ItemLineDao extends SimplePageEditStyleDao {

    /**
    * Returns the SQL select statement.
    * @return        a string containing sql query
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected String sqlSelectByPrimaryKey(){

        return sqlFind();
    }

    /**
    * Sets the values of a result set to a pk
    * @param         pStmt   the incoming prepared statement
    * @param         pk      the searching primary key
    * @return        a vo containing the result set values
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected void setValuesForSelectByPrimaryKey(PreparedStatement pStmt, SimpleBasePk pk) throws SQLException{

        setValuesForFind(pStmt, pk);
    }

    /**
    * Returns the constructed model from a result set.
    * @param         rs     the incoming result set
    * @return        a constructed VO from the result set
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected SimpleBaseVo getModelForSelectByPrimarKey(ResultSet rs) throws SQLException {

        return getResultSetValues(rs);
    }


    /**
    * Returns the SQL select statement.
    * @param         criteria     the searching criteria
    * @return        a string containing sql query
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected String sqlSelectByCriteria(SimpleBaseSrchCriteria criteria) {

        return sqlQuery(criteria);
    }

    /**
    * Returns the constructed model from a result set.
    * @param         rs     the incoming result set
    * @return        a constructed VO from the result set
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected SimpleBaseVo getModelForSelectByCriteria(ResultSet rs) throws SQLException {

        return getResultSetValues(rs);
    }

    /**
    * Returns the constructed primary key from a result set.
    * @param         rs     the incoming result set
    * @return        a constructed PK from the result set
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected SimpleBasePk getPkForSelectByCriteria(ResultSet rs) throws SQLException {

//		ErrorMsgLabelPk pk = new ErrorMsgLabelPk(rs.getString("COMPANY_CODE"),
//			rs.getString("GROUP_ID"), rs.getString("MENU_ID"), rs.getString("OBJECT_ID"),
//			rs.getString("LANG_CODE"));
//		return pk;

        return null;
    }


    /**
    * Returns the SQL select statement.
    * @param         obj     the searching criteria
    * @return        a string containing sql query
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected String sqlQuery(SimpleBaseSrchCriteria obj) {

        ItemLineSrchCriteria criteria = (ItemLineSrchCriteria) obj;
        String sql = "";
        String whereClause = toWhereClause(criteria);
        String groupByClause = "";
        String orderByClause = " ";

        if(!criteria.isSearchResult()){
            sql = "SELECT COMPANY_CODE, ITEM_LINE, ITEM_LINE_DESC, STATUS, DC_IND, "
            + "DC_DATE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE "
            + "FROM V_ITEM_LINE "
            + whereClause
            + groupByClause
            + orderByClause;
        } else {
            sql = "SELECT COMPANY_CODE,"+
            "        NODE_ID ITEM_LINE,"+
            "        - 1 ITEM_CATEGORY,"+
            "        - 1 ITEM_CLASS,"+
            "        DESCRIPTION ITEM_LINE_DESC,"+
            "        CATALOG_LEVEL,"+
            "        STATUS,"+
            "        DECODE (NVL (browser, 0), 1, 'YES', 'NO') SHOW_IN_CATALOG,"+
            "        DC_IND,"+
            "        DC_DATE,"+
            "        CREATE_USER,"+
            "        CREATE_DATE,"+
            "        UPDATE_USER,"+
            "        UPDATE_DATE"+
            " FROM   T_CATALOG "
            + whereClause
            + groupByClause
            + orderByClause;
        }
        //super.logSqlQuery(sql);
        return sql;
    }

    /**
    * Returns the where clause of a SQL select statement.
    * @param         obj     the searching criteria
    * @return        a string containing the where clause
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected String toWhereClause(ItemLineSrchCriteria crit) {

        ItemLineSrchCriteria criteria = (ItemLineSrchCriteria) crit;
        StringBuffer whereClause = new StringBuffer();

        if (criteria.getSrchCompanyCode() != null) {
            whereClause.append(whereClause.length()== 0? " WHERE ": " AND ");
            whereClause.append(" COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
        }
        
        whereClause.append(" AND CATALOG_LEVEL = 1 " );

        if (criteria.getSrchItemLineFr() != null) {
            whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
            whereClause.append(" NODE_ID >= " + criteria.getSrchItemLineFr().trim().toUpperCase()+" ");
        }

        if (criteria.getSrchItemLineTo() != null) {
            whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
            whereClause.append(" NODE_ID <= " + criteria.getSrchItemLineTo().trim().toUpperCase()+" ");
        }

        if (criteria.getSrchItemLineDescFr() != null) {
            whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
            whereClause.append(" upper(DESCRIPTION) like '%" + criteria.getSrchItemLineDescFr().trim().toUpperCase()+"%' ");
        }

        return whereClause.toString();
    }

    /**
    * Returns an array list of child VOs by the parent
    * @param         parent   the incoming parent VO
    * @param         conn     the database connection
    * @return        an array list of child VOs
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
        throws FindRecordException {

        return null;
    }

    /**
    * Constructs a string of SQL insert statement
    * @return        a string of SQL insert statement
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected String sqlInsert() {

        String sql = "INSERT INTO t_catalog ("
            + "NODE_ID, DESCRIPTION, CATALOG_LEVEL, CREATE_USER, CREATE_DATE, " 
            + "COMPANY_CODE, STATUS, BROWSER ) "
            + "VALUES ("
            + "nvl((select max(node_id) + 1 from t_catalog), 1), ?, ?, ?, ?, "
            + "?, ?, ? "
            + ")";
        //super.logSqlQuery(sql);
        return sql;
    }

    /**
    * Sets the values for a SQL insert or update statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
        throws SQLException {

        ItemLineVo vo = (ItemLineVo) obj;
        int i = 1;
//        
        if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
        	setNullableBigDecimal(pStmt, i++, CommonUtil.stringToBigDecimal(vo.getItemLine()));
        }
        setNullableString(pStmt, i++, vo.getItemLineDesc());
        pStmt.setInt(i++, 1);
        setNullableString(pStmt, i++, vo.getCreateUser());
        setNullableTimestamp(pStmt, i++, vo.getCreateDate());
        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getStatus());
        if(ProjectConstants.GENERIC_YES.equals(vo.getBrowser())){
            pStmt.setInt(i++, 1);
        }else{
        	pStmt.setInt(i++, 0);
        }
        if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
        	 pStmt.setString(i++, vo.getCompanyCode());
        	 setNullableBigDecimal(pStmt, i++, CommonUtil.stringToBigDecimal(vo.getItemLine()));
        }
        
       /* pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getItemLine());
        setNullableString(pStmt, i++, vo.getItemLineDesc());
        setNullableString(pStmt, i++, vo.getStatus());

        if (ProjectConstants.DB_OP_UPDATE.equals(opType))
            setNullableString(pStmt, i++, vo.getDcInd());
        setNullableDate(pStmt, i++, vo.getDcDate());
        setNullableString(pStmt, i++, vo.getCreateUser());

        if (ProjectConstants.DB_OP_UPDATE.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getCreateDate());

        setNullableString(pStmt, i++, vo.getUpdateUser());

        if (ProjectConstants.DB_OP_INSERT.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());

        if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setString(i++, vo.getItemLine());
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
        }*/
    }

    /**
    * Sets the values for a SQL insert statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
        throws SQLException {

        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
    }

    /**
    * Constructs a string of SQL update statement
    * @return        a string of SQL update statement
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected String sqlUpdate() {

        String sql = "UPDATE T_CATALOG SET NODE_ID=?, DESCRIPTION=?, CATALOG_LEVEL=?, CREATE_USER=?, CREATE_DATE=?, "
            + " COMPANY_CODE=?, status=?, BROWSER=? "
            + " WHERE COMPANY_CODE=? AND NODE_ID=? ";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
    * Sets the values for a SQL update statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected void setValuesForModify(PreparedStatement pStmt, Object obj)
        throws SQLException {

        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
    }

    /**
    * Constructs a string of SQL delete statement
    * @return        a string of SQL update statement
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected String sqlDelete() {
        String sql = "DELETE FROM T_catalog WHERE node_id = ? and company_code = ?";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
    * Sets the values for a SQL delete statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
        throws SQLException {

        ItemLineVo vo = (ItemLineVo) obj;
        int i = 1;

        
        setNullableBigDecimal(pStmt, i++, CommonUtil.stringToBigDecimal(vo.getItemLine()));
        pStmt.setString(i++, vo.getCompanyCode());
        
    }

    /**
    * Constructs a string of SQL select statement
    * @return        a string of SQL select statement
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected String sqlFind() {

        String  sql = "SELECT COMPANY_CODE,"+
        "        NODE_ID ITEM_LINE,"+
        "        - 1 ITEM_CATEGORY,"+
        "        - 1 ITEM_CLASS,"+
        "        DESCRIPTION ITEM_LINE_DESC,"+
        "        CATALOG_LEVEL,"+
        "        STATUS,"+
        "        DECODE (NVL (browser, 0), 1, 'YES', 'NO') SHOW_IN_CATALOG,"+
        "        DC_IND,"+
        "        DC_DATE,"+
        "        CREATE_USER,"+
        "        CREATE_DATE,"+
        "        UPDATE_USER,"+
        "        UPDATE_DATE"+
        " FROM   T_CATALOG "
        + " WHERE COMPANY_CODE=? AND NODE_ID=? ";
        return sql;
    }

    /**
    * Sets the values for a SQL select statement for finding by primary key.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming PK
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected void setValuesForFind(PreparedStatement pStmt, Object obj)
        throws SQLException {

        ItemLinePk pk = (ItemLinePk) obj;
        int i = 1;

        pStmt.setString(i++, pk.getCompanyCode());
        if("<To be generated.>".equals(pk.getItemLine())){
        	pStmt.setInt(i++, -99);
        }else{
        	pStmt.setInt(i++, Integer.parseInt(pk.getItemLine()));
        }
        
    }

    /**
    * Constructs a string of SQL select statement
    * @return        a string of SQL select statement
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected String sqlList() {

        return null;
    }

    /**
    * Sets the values for a SQL select statement for finding child tables.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming parent PK
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected void setValuesForList(PreparedStatement pStmt, Object obj)
        throws SQLException {

    }

    /**
    * Sets the values of a result set to a vo for a search screen
    * @param         rs   the incoming result set
    * @return        a vo containing the result set values
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected SimpleBaseVo getSearchResultSetValues(ResultSet rs) throws SQLException {

        ItemLineVo vo = new ItemLineVo();

        vo.setCompanyCode(rs.getString("COMPANY_CODE"));
        vo.setItemLine(rs.getString("ITEM_LINE"));
        vo.setItemLineDesc(rs.getString("ITEM_LINE_DESC"));
        vo.setStatus(rs.getString("STATUS"));
        vo.setBrowser(rs.getString("SHOW_IN_CATALOG"));
        vo.setDcInd(rs.getString("DC_IND"));
        vo.setDcDate(CustomSqlDate.customize(rs.getDate("DC_DATE")));
        vo.setCreateUser(rs.getString("CREATE_USER"));
        vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        vo.setUpdateUser(rs.getString("UPDATE_USER"));
        vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        vo.setBrowser(rs.getString("SHOW_IN_CATALOG"));

        return vo;
    }

    /**
    * Sets the values of a result set to a vo
    * @param         rs   the incoming result set
    * @return        a vo containing the result set values
    *
    * @author     Alex Cheung
    * @since      FrameWork 1.0
    */
    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

        ItemLineVo vo = new ItemLineVo();

        vo.setCompanyCode(rs.getString("COMPANY_CODE"));
        vo.setItemLine(rs.getString("ITEM_LINE"));
        vo.setItemLineDesc(rs.getString("ITEM_LINE_DESC"));
        vo.setStatus(rs.getString("STATUS"));
        vo.setBrowser(rs.getString("SHOW_IN_CATALOG"));
        vo.setDcInd(rs.getString("DC_IND"));
        vo.setDcDate(CustomSqlDate.customize(rs.getDate("DC_DATE")));
        vo.setCreateUser(rs.getString("CREATE_USER"));
        vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        vo.setUpdateUser(rs.getString("UPDATE_USER"));
        vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));

        return vo;
    }

}
